package com.logos.jdbc.advanced;

import com.logos.jdbc.advanced.entity.Employee;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class JDBCTestAdvanced {
    private static final String url = "jdbc:mysql://192.168.227.133:3306/jdbcTest";

    public static final String userName = "root";

    public static final String passWord = "123456";

    @Test
    public void testORM() throws Exception {
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        PreparedStatement preparedStatement = connection.prepareStatement("select  emp_id, emp_name, emp_salary, emp_age from emp where emp_id = ?");
        preparedStatement.setInt(1, 5);
        ResultSet resultSet = preparedStatement.executeQuery();
        Employee employee = null;
        while (resultSet.next()) {
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            employee = new Employee(empId, empName, empSalary, empAge);
        }
        System.out.println(employee);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testORMList() throws Exception {
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        PreparedStatement preparedStatement = connection.prepareStatement("select  emp_id, emp_name, emp_salary, emp_age from emp");
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Employee> employeeList = new ArrayList<>();
        Employee employee = null;
        while (resultSet.next()) {
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            employee = new Employee(empId, empName, empSalary, empAge);
            employeeList.add(employee);
        }
        for (Employee emp : employeeList) {
            System.out.println(emp);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testORMPK() throws Exception {
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        String sql = "insert into emp(emp_name, emp_salary, emp_age) VALUES (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        Employee employee = new Employee(null, "rose", 666.66, 19);
        preparedStatement.setString(1, employee.getEmpName());
        preparedStatement.setDouble(2, employee.getEmpSalary());
        preparedStatement.setInt(3, employee.getEmpAge());
        int result = preparedStatement.executeUpdate();
        if (result > 0) {
            System.out.println("success");
            ResultSet resultSet = preparedStatement.getGeneratedKeys();
            int empId = 0;
            if (resultSet.next()) {
                empId = resultSet.getInt(1);
                employee.setEmpId(empId);
            }
            System.out.println(employee);
            resultSet.close();
        } else {
            System.out.println("fail");
        }
        preparedStatement.close();
        connection.close();
    }

    //costTimes:31062ms
    @Test
    public void testORMMoreInsert() throws Exception {
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        String sql = "insert into emp(emp_name, emp_salary, emp_age) VALUES (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "rose" + i);
            preparedStatement.setDouble(2, 100.00 + i);
            preparedStatement.setInt(3, 1 + i);
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("costTimes:" + (end - start) + "ms");
        preparedStatement.close();
        connection.close();
    }

    //costTimes:1579ms
    @Test
    public void testORMBatch() throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.227.133:3306/jdbcTest?rewriteBatchedStatements=true",
                userName, passWord);
        String sql = "insert into emp(emp_name, emp_salary, emp_age) VALUES (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "rose" + i);
            preparedStatement.setDouble(2, 100.00 + i);
            preparedStatement.setInt(3, 1 + i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("costTimes:" + (end - start) + "ms");
        preparedStatement.close();
        connection.close();
    }
}
